import datetime

from ncatbot.plugin import BasePlugin, CompatibleEnrollment
from ncatbot.core import GroupMessage, MessageArray, BotClient
from ncatbot.plugin_system import NcatBotPlugin
import sys
import os
from .utils import *

sys.path.append(os.path.dirname(__file__))
from mysql_assistant import MySQLAssistant
from utils import load_config_from_yaml

bot = CompatibleEnrollment  # 兼容回调函数注册器
config = load_config_from_yaml("my_config.yaml")
bot_id = config.get("bot_id")
bot_name = config.get("bot_name")
interval = config.get("crawl_interval")
group_id_list = config.get("crawl_group_id_list")


class UUCrawl(BasePlugin):
    name = "UUCrawl"  # 插件名称
    version = "0.0.1"  # 插件版本
    author = "Ethan Ye"
    info = "悠悠爬虫，每小时爬取市场行情"
    description = "悠悠爬虫，每小时爬取市场行情"

    plugin_dir = "./plugins"
    mysql = MySQLAssistant(config_file="my_config.yaml")

    create_crawl_table_sql = """
    CREATE TABLE IF NOT EXISTS UUCrawl (
        id INT AUTO_INCREMENT PRIMARY KEY,
        template_id VARCHAR(32) NOT NULL,
        name VARCHAR(32),
        min_price FLOAT(16,2),
        on_sale_count INT(8),
        on_lease_count INT(8),
        lease_unit_price FLOAT(16,2),
        long_lease_unit_price FLOAT(16,2),
        lease_deposit FLOAT(16,2),
        valid INT(2) NOT NULL,
        group_id VARCHAR(32) NOT NULL,
        date DATE,
        qq_numbers VARCHAR(128) NOT NULL,
        UNIQUE KEY (id),
        INDEX uukey (template_id, group_id, qq_numbers)
    )
    """

    create_quant_table_sql = """
    CREATE TABLE IF NOT EXISTS UUCrawlQuant (
        id INT AUTO_INCREMENT PRIMARY KEY,
        template_id INT(4) NOT NULL,
        type VARCHAR(32) NOT NULL,
        name VARCHAR(64),
        min_price FLOAT(16,2),
        on_sale_count INT(8),
        on_lease_count INT(8),
        lease_unit_price FLOAT(16,2),
        long_lease_unit_price FLOAT(16,2),
        lease_deposit FLOAT(16,2),
        date DATETIME,
        UNIQUE KEY (id),
        INDEX uukey (template_id),
        INDEX time (date)
    )
    """

    async def on_load(self):
        print(f"{self.name} 插件已加载")
        print(f"插件版本: {self.version}")

        self.add_scheduled_task(
            self.crawl,
            "crawl",
            "1h",
            conditions=[self.is_active]
        )

        self.add_scheduled_task(
            self.quant,
            "quant",
            "2h"
        )

        self.add_scheduled_task(
            self.merge_data,
            "merge_data",
            "00:00"
        )

    async def crawl(self):
        self.mysql.connect()
        self.mysql.create_table_if_not_exists("UUCrawl", create_table_sql=self.create_crawl_table_sql)

        records = self.mysql.execute_query(f"""
            SELECT DISTINCT template_id, group_id, qq_numbers FROM UUCrawl WHERE valid = %s
        """, 1)

        goods_list = []
        goods_owner = {}
        for item in records:
            if item['template_id'] not in goods_list:
                goods_list.append(item['template_id'])
            if item['template_id'] not in goods_owner:
                goods_owner[(item['template_id'], item['group_id'])] = item['qq_numbers']

        insert2mysql = []
        res = crawl_goods(goods_list, interval)

        for item in res:
            group_list = []
            for record in records:
                if record['group_id'] in group_list:
                    continue
                if (item.template_id, record['group_id']) in goods_owner:
                    group_list.append(record['group_id'])
                    insert2mysql.append({
                        'template_id': item.template_id,
                        'name': item.name,
                        'min_price': item.min_price,
                        'on_sale_count': item.on_sale_count,
                        'on_lease_count': item.on_lease_count,
                        'lease_unit_price': item.lease_unit_price,
                        'long_lease_unit_price': item.long_lease_unit_price,
                        'lease_deposit': item.lease_deposit,
                        'valid': 1,
                        'group_id': record['group_id'],
                        'date': item.time,
                        'qq_numbers': goods_owner[(item.template_id, record['group_id'])]
                    })
                    msg = MessageArray()

                    for qq_number in goods_owner[(item.template_id, record['group_id'])].split(','):
                        msg.add_at(qq_number)

                    msg.add_text(f"""
爬取时间：{item.time}
物品：{item.name}
悠悠id：{item.template_id}
在售底价：{item.min_price}
在售数：{item.on_sale_count}
在租数：{item.on_lease_count}
短租价/天：{item.lease_unit_price}
长租价/天：{item.long_lease_unit_price}
押金：{item.lease_deposit}""")
                    await self.api.post_group_array_msg(record['group_id'], msg)

        self.mysql.insert_data("UUCrawl", insert2mysql)

    async def quant(self):
        self.mysql.connect()
        self.mysql.create_table_if_not_exists("UUCrawlQuant", create_table_sql=self.create_quant_table_sql)

        records = self.mysql.execute_query(f"""
            SELECT DISTINCT template_id FROM UUCrawlQuant
        """)
        if len(records) == 0:
            return

        goods_list_slice = [item['template_id'] for item in records]
        goods_list_slice = [goods_list_slice[i:i + 50] for i in range(0, len(goods_list_slice), 50)]
        for goods_list in goods_list_slice:
            retry_cnt = 0
            while retry_cnt <= 3:
                try:
                    res = crawl_goods(goods_list, interval)
                    insert2mysql = []
                    for item in res:
                        insert2mysql.append({
                            'template_id': item.template_id,
                            'name': item.name,
                            'type': item.type,
                            'min_price': item.min_price,
                            'on_sale_count': item.on_sale_count,
                            'on_lease_count': item.on_lease_count,
                            'lease_unit_price': item.lease_unit_price,
                            'long_lease_unit_price': item.long_lease_unit_price,
                            'lease_deposit': item.lease_deposit,
                            'date': item.time
                        })
                    self.mysql.insert_data("UUCrawlQuant", insert2mysql)
                    break
                except Exception as e:
                    retry_cnt += 1
                    time.sleep(interval)
                    print(retry_cnt, e)

        latest = self.analyze(f"""
            SELECT 
                *
            FROM (
                select
                    *,
                    row_number() over (partition by template_id order by date desc) rn
                from UUCrawlQuant 
            ) t
            where rn <= 2
        """)
        for group_id in group_id_list:
            if len(latest['up']) != 0:
                await self.api.post_group_array_msg(group_id, self.pack_message(latest, 'up'))
            if len(latest['down']) != 0:
                await self.api.post_group_array_msg(group_id, self.pack_message(latest, 'down'))

        for idx in [1, 3, 7, 30, 90, 180]:
            res = self.analyze(f"""
                SELECT 
                    *
                FROM (
                    select
                        *,
                        row_number() over (partition by template_id order by date desc) rn
                    from UUCrawlQuant 
                ) t
                where rn = 1
                
                union all
                
                select
                    id, 
                    template_id, 
                    type, 
                    name, 
                    min_price, 
                    on_sale_count, 
                    on_lease_count, 
                    lease_unit_price, 
                    long_lease_unit_price, 
                    lease_deposit, 
                    date, 
                    2 as rn
                from (
                    SELECT
                        *,
                        row_number() over (partition by template_id order by date desc) rn
                    FROM UUCrawlQuant
                    WHERE substr(date, 1, 10) = DATE_SUB(CURDATE(), INTERVAL {idx} DAY)
                ) t
                where rn = 1
            """)

            for group_id in group_id_list:
                if len(res['up']) != 0:
                    await self.api.post_group_array_msg(group_id, self.pack_message(res, 'up', idx))
                if len(res['down']) != 0:
                    await self.api.post_group_array_msg(group_id, self.pack_message(res, 'down', idx))

    def is_active(self):
        return 0 <= datetime.datetime.now().hour <= 2 or 8 <= datetime.datetime.now().hour <= 24

    def merge_data(self):
        self.mysql.connect()
        self.mysql.execute_query(f"""
            DELETE t 
            FROM UUCrawlQuant t
            JOIN (
                SELECT
                    id,
                    ROW_NUMBER() OVER (PARTITION BY template_id, substr(date, 1, 10) ORDER BY date DESC) AS rn
                FROM
                    UUCrawlQuant
                WHERE
                    date < CURDATE()
            ) sub
            ON t.id = sub.id AND rn <> 1
        """)

    def analyze(self, sql):
        self.mysql.connect()
        records = self.mysql.execute_query(sql)
        if len(records) == 0:
            return

        analyze_dict = {}
        analyze_list = []
        for record in records:
            if record['template_id'] not in analyze_list:
                analyze_list.append(record['template_id'])
            if (record['template_id'], record['rn']) not in analyze_dict:
                analyze_dict[(record['template_id'], record['rn'])] = {
                    'name': record['name'],
                    'template_id': record['template_id'],
                    'min_price': record['min_price'],
                    'on_sale_count': record['on_sale_count'],
                    'date': record['date']
                }

        res_list = {
            'up': [],
            'down': []
        }

        gap = 0.1
        for item in analyze_list:
            if (item, 1) in analyze_dict and (item, 2) in analyze_dict:
                res = calculate(analyze_dict[(item, 1)], analyze_dict[(item, 2)])
                if res['delta_price_rate'] >= gap:
                    res_list['up'].append(res)
                if res['delta_price_rate'] <= -gap:
                    res_list['down'].append(res)

        res_list['up'] = sorted(res_list['up'], key=lambda x: x['delta_price_rate'], reverse=True)[:10]
        res_list['down'] = sorted(res_list['down'], key=lambda x: x['delta_price_rate'])[:10]

        return res_list

    def pack_message(self, analyze_res, type_, day=0):
        msg = MessageArray()
        text = '涨' if type_ == 'up' else '降'

        if day == 0:
            msg.add_text(f'最近{text}幅前{len(analyze_res[type_])}的物品是：')
        else:
            msg.add_text(f'最近{day}日{text}幅前{len(analyze_res[type_])}的物品是：')
        for item in analyze_res[type_]:
            msg.add_text(f"""
            
物品：{item['name']}
悠悠id：{item['template_id']}
现在底价：{item['min_price']}
上次底价：{item['last_min_price']}
{text}幅：{round(item['delta_price_rate'] * 100, 2)}%
{text}额：{round(item['delta_price'], 2)}
在售数：{item['on_sale']}
在售数变化：{item['delta_sale_count']}""")

        return msg
